#!/usr/local/bin/python
# -*- coding: utf-8 -*
import xlrd
import json
import codecs
import os
import sys	 


application_path = os.path.dirname(__file__)
# outpath = "/assets/resources/configs/sheets"
outpath = "/Users/mu/work/gitee/cocos-creator-plugin/xlsx2json/out/Json"
 
def ExcelToJson(file_path):
    print("打开Excel文件:" + file_path)
    if get_data(file_path) is not None:
        book = get_data(file_path)
 
        # 获取sheet页
        worksheets = book.sheet_names()
 
        remove_target = "./#.xlsx#.xls"
        target_list = str.split(remove_target,"#")
 
        file_name = file_path.split('/')[-1]
 
        for target in target_list:
            if file_name.find(target)!=-1:
                file_name = file_name.replace(target,"")
  
        for sheet in worksheets:
            if (worksheets.index(sheet) != 0):
                return
            # print('%s,%s' % (worksheets.index(sheet), sheet))
            sheet_index = worksheets.index(sheet)
            sheet = book.sheet_by_index(int(sheet_index))
            row_titles = sheet.row(0)  # 表单标题集
            row_types = sheet.row(1) #数据类型
            nrows = sheet.nrows  # 行号集
            ncols = sheet.ncols  # 列号集
 
            result_file_name = str(file_name)
 
            # print("resulat_file_name:"+result_file_name)
 
            if IsHaveRepeatFile(row_titles):
                print("表格："+file_path+"&表格页："+sheet.name+" 有重复字段！！！！！！")
                return
	 
            result = []  # 定义json对象
            # result[result_file_name] = [] #设置数组名
            # 遍历所有行，将excel转化为json对象
            for i in range(nrows):
                if i == 0 or i == 1 or i == 2: #说明文字 && 属性字段 &&数据类型 排除
                    continue
                temp = {}
                # 遍历当前行所有列
                for j in range(ncols):
                    # 获取当前列中文标题
                    title_de = str(row_titles[j])
 
                    # 获取数据类型
                    dataType = str(row_types[j])
                    type = dataType.split("'")[1]
                    #print(str(title_de))
                    #删除"'"字符
                    title_cn = title_de.split("'")[1]
                    # 获取单元格的值
                    resulat_tip = GetValue(type,sheet.row_values(i)[j])
 
 
                    temp[title_cn] = resulat_tip
 
                result.append(temp)
            #dumps 参数说明
            #indent 缩进字符数
            #sort_keys 是否对字段进行排序
 
            #以python内编码存储中文  显示的中文字如
            #json_data = json.dumps(result, indent=4, sort_keys=False)
 
            #正常显示中文字
            json_data = json.dumps(result, indent=4, sort_keys=False,ensure_ascii=False)
 
            #保存数据
            saveFile(str(outpath), str(result_file_name), json_data)
 
def GetValue(type, oldValues):
    # print("type::"+str(oldValues))
    if oldValues == '':
        return None
    if type.lower() == "int":
        return int(oldValues)
    if type.lower() == 'string':
        return str(oldValues)
    if type.lower() == 'float':
        return float(oldValues)
    if type.lower() == 'boolean':
        if oldValues == 1:
            return  True
        else:
            return  False
    if type.find("[]"):
        t = type[:type.find("[]")]
        oldValues = str(oldValues)
        print(t, oldValues)
        if t.find("[]") != -1:
            # 二维数组
            return None
        else:
            # 一维数组
            l = []
            if oldValues.find(",") != -1:
                l = oldValues.split(',')
                return getArrayData(l, t.lower())
            else:
                if t.lower() == "int":
                    l.append(int(float(oldValues)))
                if t.lower() == "string":
                    l.append(oldValues)
                if t.lower() == "boolean":
                    l.append(int(float(oldValues)) == 1)
                if t.lower() == "float":
                    l.append(float(oldValues))
                print("x", t, oldValues, l)
                return l

def getArrayData(ldata, type):
    arr = []
    for d in ldata:
        print("s", d, int(d))
        if type == "int":
            arr.append(int(d))
        if type == "string":
            arr.append(str(d))
        if type == "boolean":
            arr.append(int(d) == 1)
        if type == "float":
            arr.append(float(d))
    print("s", type, ldata, arr)
    return arr

 
#判断是否有重复字段
def IsHaveRepeatFile(data):
    is_have = False
    #获取所有字段数组
    tempData = []
    index = 0
    for d  in data:
        tempData.append(str(d).split("'")[1])
    #字段个数统计
    countDict = {}
    for x in set(tempData):
        countDict[x] = list.count(tempData,x)
    #print(str(countDict))
    #判断是不有重复字段
    for temp in countDict:
        if countDict[temp]>1:
            is_have = True
            print("重复字段："+str(temp))
    return is_have
#获取数据
def get_data(file_path):
    try:
        data = xlrd.open_workbook(file_path)
        return data
    except Exception as e:
        print("获取excel数据表错误:" + file_path)
        print(str(e))
        return None
 
#保存文件
def saveFile(file_path, file_name, data):
    print(str("保存文件："+file_path + "/" + file_name + ".json"))
 
    if os.path.exists(file_path) != True:
        #创建文件夹out
        os.makedirs(file_path)
 
    output = codecs.open(file_path + "/" + file_name + ".json", 'w', "utf-8")
    output.write(data)
    output.close()
 
#获取目录下所有文件
def getListFiles(path):
    ret = []
    for root, dirs, files in os.walk(path):
        for filespath in files:
            ret.append(os.path.join(root, filespath))
    return ret
 
 
if __name__ == '__main__':
    os.chdir(application_path)

    with open('xmls', 'r', encoding='utf8') as fp:
        file_path = fp.readline()
        while (file_path[:-1] != ''):
            if file_path[:-1].endswith(".xlsx"):
            # or file_path[:-1].endswith(".xls"):
                json_data = ExcelToJson(os.path.join("./Xlsx", file_path[:-1]))
            else:
                print("xml文件路径不合法: "+file_path)
            file_path = fp.readline()
        fp.close()
    print("数据导出成功！！！")
